package com.star.robot.service;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.star.robot.entity.*;
import com.star.robot.enums.ProjectLevelEnum;
import com.star.robot.repository.*;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.star.robot.enums.CompanyTypeEnum;
import com.star.robot.enums.ProjectClassEnum;
import com.star.robot.enums.TeamClotherSizeEnum;
import com.star.robot.util.StringUtil;

@Service
public class ScoreService {
	@Autowired
    private CompanyRepository companyRepository;
	private HSSFWorkbook workbook;
	
    @Autowired
    private TeamRepository teamRepository;

    @Autowired
    private TeamMemberRepository teamMemberRepository;
    @Autowired
    private TeamLeaderRepository teamLeaderRepository;
    @Autowired
    private ProjectRepository projectRepository;
    @Autowired
	private TeamScoreRepository teamScoreRepository;
    @Autowired
	private UserRepositoty userRepositoty;

	@Transactional
	public String batchImport(String fileName, MultipartFile file) throws Exception {
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new Exception("上传文件格式不正确");
		}
		//List<Company> companyList = new ArrayList<>();
		try {
			InputStream is = file.getInputStream();
			workbook = new HSSFWorkbook(new POIFSFileSystem(is));
			// 有多少个sheet
			int sheets = workbook.getNumberOfSheets();

			for (int i = 0; i < sheets; i++) {
				HSSFSheet sheet = workbook.getSheetAt(i);
				// 获取多少行
				int rows = sheet.getPhysicalNumberOfRows();
				Company company = null;
				Team team = null;
				Project project = null;
				// 遍历每一行，注意：第 0 行为标题
				for (int j = 1; j < rows; j++) {
					company = new Company();
					team = new Team();
					project = new Project();
					company = new Company();
					// 获得第 j 行
					HSSFRow row = sheet.getRow(j);
					//单位bean
					System.out.println("row=" + row);
					//学校
					company.setCompanyType("学校".equals(StringUtil.getCellValue(row.getCell(0)))?CompanyTypeEnum.XUEXIAO:CompanyTypeEnum.JIGOU);
					//
					company.setName(StringUtil.getCellValue(row.getCell(1)));
					//companyList.add(company);
					//

					//队伍
					team.setTeamName(StringUtil.getCellValue(row.getCell(2)));
					team.setCreateTime(new Date());
					team.setConfirmStatus(true);
					team.setTemplateId(-1L);


					//
					project.setName(StringUtil.getCellValue(row.getCell(3)));
					project.setDescr(StringUtil.getCellValue(row.getCell(3)));
					project.setStatus(true);
					project.setProjectLevelEnum(ProjectLevelEnum.PROVINCELEVEL);
					Project oldProject = projectRepository.findByName(project.getName());
					if(oldProject != null){
						team.setProject(oldProject);
					}else{
						projectRepository.save(project);
						team.setProject(project);
					}
					//
					if ("小学组".equals(StringUtil.getCellValue(row.getCell(4)))) {
						team.setProjectClassEnum(ProjectClassEnum.XIAOXUE);
					}else if("初中组".equals(StringUtil.getCellValue(row.getCell(4)))) {
						team.setProjectClassEnum(ProjectClassEnum.CHUZHONG);
					}else {
						team.setProjectClassEnum(ProjectClassEnum.GAOZHONG);
					}
					//
					List<TeamLeader> teamLeaders = new ArrayList<TeamLeader>();
					TeamLeader teamLeader = new TeamLeader();
					teamLeader.setName(StringUtil.getCellValue(row.getCell(7)).split("、")[0]);

					//辅导老师
					if (StringUtil.getCellValue(row.getCell(7)).split("、").length>1) {
						TeamLeader teamLeader2 = new TeamLeader();
						teamLeader2.setName(StringUtil.getCellValue(row.getCell(7)).split("、")[1]);
						teamLeader2.setPhone(StringUtil.getCellValue(row.getCell(9)));
						teamLeaders.add(teamLeader2);
					}

					//
					teamLeader.setPhone(StringUtil.getCellValue(row.getCell(8)));
					teamLeaders.add(teamLeader);
					team.setTeamLeaders(teamLeaders);

					//队员所在学校

					String allSchool = StringUtil.getCellValue(row.getCell(6));
					String[] schoolSplit = allSchool.split("、");
					if (schoolSplit.length > 1) {

					}


					
					//队员
					List<TeamMember> teamMembers = new ArrayList<>();
					TeamMember teamMember1 = TeamMember.builder().name(StringUtil.getCellValue(row.getCell(10))).idCard(StringUtil.getCellValue(row.getCell(11))).build();
					TeamMember teamMember2 = TeamMember.builder().name(StringUtil.getCellValue(row.getCell(13))).idCard(StringUtil.getCellValue(row.getCell(14))).build();
					TeamMember teamMember3 = TeamMember.builder().name(StringUtil.getCellValue(row.getCell(16))).idCard(StringUtil.getCellValue(row.getCell(17))).build();
					TeamMember teamMember4 = TeamMember.builder().name(StringUtil.getCellValue(row.getCell(19))).idCard(StringUtil.getCellValue(row.getCell(20))).build();
					teamMember1.setSize(getClothesSize(StringUtil.getCellValue(row.getCell(12))));
					teamMember2.setSize(getClothesSize(StringUtil.getCellValue(row.getCell(15))));
					teamMember3.setSize(getClothesSize(StringUtil.getCellValue(row.getCell(18))));
					teamMember4.setSize(getClothesSize(StringUtil.getCellValue(row.getCell(21))));
					//默认男
					teamMember1.setSex(1);
					teamMember2.setSex(1);
					teamMember3.setSex(1);
					teamMember4.setSex(1);
					if(!StringUtils.isEmpty(teamMember1.getName())){

						teamMember1.setSchool(schoolSplit[0]);
						teamMembers.add(teamMember1);
					}
					if(!StringUtils.isEmpty(teamMember2.getName())){
						teamMember2.setSchool(schoolSplit[0]);
						if (schoolSplit.length > 1) {
							teamMember2.setSchool(schoolSplit[1]);
						}
						teamMembers.add(teamMember2);
					}
					if(!StringUtils.isEmpty(teamMember3.getName())){
						teamMember3.setSchool(schoolSplit[0]);
						if (schoolSplit.length > 2) {
							teamMember3.setSchool(schoolSplit[2]);
						}
						teamMembers.add(teamMember3);
					}
					if(!StringUtils.isEmpty(teamMember4.getName())){
						teamMember4.setSchool(schoolSplit[0]);
						if (schoolSplit.length > 3) {
							teamMember4.setSchool(schoolSplit[3]);
						}
						teamMembers.add(teamMember4);
					}
					team.setTeamMembers(teamMembers);
					
					//fapiao
					team.setFapiaoStatus(StringUtil.getCellValue(row.getCell(22)).equals("是") ? true : false);
					
					//单位全称
					company.setZhiZhao(StringUtil.getCellValue(row.getCell(23)));
					
					//纳税人识别号
					company.setInvoiceTaxNo(StringUtil.getCellValue(row.getCell(24)));
					company.setStatus(true);

					//地区id
					String areaId = StringUtil.getCellValue(row.getCell(33));
					company.setCityId(Integer.parseInt(areaId));

					Company oldCompany = companyRepository.findByName(company.getName());
					if(oldCompany == null) {
						this.companyRepository.save(company);
					}else {
						company.setId(oldCompany.getId());
						this.companyRepository.save(company);
					}
					
					team.setCompany(company);

					Team dbTeam = teamRepository.findByTeamNameAndCompany_Id(team.getTeamName() , company.getId());
					if(dbTeam == null){
						this.teamRepository.save(team);
					}else{
						team.setId(dbTeam.getId());
						this.teamRepository.save(team);
					}


					if(!CollectionUtils.isEmpty(teamMembers)) {

						for(TeamMember teamMember : teamMembers) {


							TeamMember oldMember = this.teamMemberRepository.findByIdCard(teamMember.getIdCard());
							teamMember.setTeam(team);
							if(oldMember == null) {
								this.teamMemberRepository.save(teamMember);
							}else {
								teamMember.setId(oldMember.getId());
								this.teamMemberRepository.save(teamMember);
							}
						}
						
					}
					
					if(!CollectionUtils.isEmpty(teamLeaders)) {
						addFrontUser(teamLeader,company);
						for(TeamLeader tempLeader : teamLeaders) {


							tempLeader.setTeam(team);
//							TeamLeader oldlLeader = this.teamLeaderRepository.findByPhone(tempLeader.getPhone());

							this.teamLeaderRepository.save(tempLeader);

//							if(oldlLeader == null) {
//								this.teamLeaderRepository.save(tempLeader);
//							}else {
//								tempLeader.setId(oldlLeader.getId());
//								this.teamLeaderRepository.save(tempLeader);
//							}
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
		return "导入数据成功";
	}


	private void addFrontUser(TeamLeader teamLeader,Company company) {
		System.out.println(teamLeader.toString());
		User oldUser = userRepositoty.findByPhone(teamLeader.getPhone());
		if(oldUser == null){
			userRepositoty.save(User.builder()
					.cityId(!StringUtils.isEmpty(company.getCityId()) ?Integer.valueOf(company.getCityId()) : null)
					.areaId(!StringUtils.isEmpty(company.getAreaId()) ?Integer.valueOf(company.getAreaId()) : null)
					.account(null)
					.company(company)
					.phone(teamLeader.getPhone())
					.realName(teamLeader.getName())
					.passwd(getPassword(teamLeader.getPhone()))
					.modifyPwd(0)
					.build());
		}
	}

	private String getPassword(String name) {
		return name.substring(name.length() - 6 , name.length());
	}


	public TeamClotherSizeEnum getClothesSize(String sizeStr) {
		if (sizeStr.startsWith("S")) {
			return TeamClotherSizeEnum.S;
		}else if(sizeStr.startsWith("M")) {
			return TeamClotherSizeEnum.M;
		}else if(sizeStr.startsWith("L")){
			return TeamClotherSizeEnum.L;
		}else if(sizeStr.startsWith("XL")){
			return TeamClotherSizeEnum.XL;
		}else if(sizeStr.startsWith("XXXL")){
			return TeamClotherSizeEnum.XXXL;
		}
		return TeamClotherSizeEnum.XXXL;
	}

	/**
	 * 导入成绩
	 * @param fileName
	 * @param file
	 * @return
	 * @throws Exception
	 */
	@Transactional(readOnly = false, rollbackFor = Exception.class)
    public Object importScore(String fileName, MultipartFile file)throws Exception {
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new Exception("上传文件格式不正确");
		}
		//List<Company> companyList = new ArrayList<>();
		try {
			InputStream is = file.getInputStream();
			workbook = new HSSFWorkbook(new POIFSFileSystem(is));
			// 有多少个sheet
			int sheets = workbook.getNumberOfSheets();


			for (int i = 0; i < sheets; i++) {
				HSSFSheet sheet = workbook.getSheetAt(i);
				// 获取多少行
				int rows = sheet.getPhysicalNumberOfRows();
				Company company = null;
				Team team = null;
				Project project = null;
				// 遍历每一行，注意：第 0 行为标题
				for (int j = 1; j < rows; j++) {
					company = new Company();
					team = new Team();
					project = new Project();
					company = new Company();
					// 获得第 j 行
					HSSFRow row = sheet.getRow(j+1);
					//单位bean
					System.out.println("row=" + row);
					//队伍id

					if(StringUtils.isEmpty(row.getCell(2))){
						continue;
					}
					Long teamId = Long.valueOf(StringUtil.getCellValue(row.getCell(2))  );
					//轮次
					Long lunci = Long.parseLong(StringUtil.getCellValue(row.getCell(6)));
					//成绩
					Float score = Float.valueOf(StringUtil.getCellValue(row.getCell(7)));

					Team oldTeam =teamRepository.findById(teamId).get();
					if(oldTeam == null ){
						return "false";
					}

					TeamScore teamScore = teamScoreRepository.findByTeam_IdAndLunCi(teamId , lunci);
					TeamScore newScore = TeamScore.builder()
							.team(oldTeam)
							.status(false)
							.lunCi(lunci)
							.score(score)
							.build();
					if(teamScore != null){
						teamScoreRepository.updateScore(score, teamId, lunci);
					}else{
						teamScoreRepository.save(newScore);
					}

				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "导入数据格式有误，请检查上传文件";
		}
		return "导入数据成功";
    }
}
